#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
create database if not exists  oe_dwb;
drop table if exists oe_dwb.dwb_customer_relationship_detail;
create table oe_dwb.dwb_customer_relationship_detail
(
-- 客户信息表
    customer_relationship_id       int comment '当前意向id',
    origin_type                    string comment '数据来源-线上线下',
    origin_channel                 string comment '来源渠道',
    area                     string comment '区域',
--  线索申诉信息表
    customer_relationship_first_id int comment '第一条客户关系id',
    appeal_status                  int comment '申诉状态，0:待稽核 1:无效 2：有效',
    create_date_time               timestamp comment '创建时间',
    update_date_time               timestamp comment '更新时间',
-- 校区信息表
    school_id                      int comment '唯一标识符',
    school_name                    string comment '校区名称',
-- 学科信息表
    subject_id                     int comment '唯一标识符',
    subject_name                   string comment '学科名称',
-- 员工部门表
    dp_id               int comment '部门id',
    name             string comment '部门名称',
    tdepart_code                int comment '直属部门',
-- 客户线索表
    id                      int comment '线索id',
    customer_id              int comment '客户id',


    customer_state           string comment '当前客户状态',
    clue_state               string comment '线索状态-新老',
    is_repeat                int comment '是否重复线索(手机号维度) 0:正常 1：重复',
-- 客户意向表
    business_scrm_department_id int comment '归属部门',
    first_customer_clue_id      int comment '第一条线索ID',
    appeal_id                   int comment '申诉id'
)
COMMENT '客户意向宽表'
partitioned by (dt string comment '日期')
row format delimited fields terminated by '\t'
stored as textfile
tblproperties ('textfile.compress' = 'SNAPPY');



set hive.auto.convert.join=false;
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;
insert into oe_dwb.dwb_customer_relationship_detail partition (dt)
select
  c.customer_relationship_id ,
  c.origin_type,
  c.origin_channel,
  c.area,
  ca.customer_relationship_first_id,
  ca.appeal_status,
  cr.create_date_time,
  ca.update_date_time,
  sh.id as school_id,
  sh.name as school_name,
  su.id as subject_id,
  su.name as subject_name,
  sd.id  as dp_id,
  sd.name,
  sd.tdepart_code,
  cc.id,
  cc.customer_id,


  cc.customer_state,
  cc.clue_state,
  cc.is_repeat,
  cr.business_scrm_department_id,
  cr.first_customer_clue_id,
  cr.appeal_id,
  substring(cr.create_date_time,1,10) as dt
from  oe_dwd.fact_t_customer_relationship cr
left join oe_dwd.fact_t_customer_clue cc on cr.customer_id=cc.customer_id
left join oe_dwd.dim_t_scrm_department sd on cr.business_scrm_department_id=sd.id
left join oe_dwd.dim_t_itcast_school sh  on cr.itcast_school_id= sh.id
left join oe_dwd.dim_t_itcast_subject su on cr.itcast_subject_id=su.id
left join oe_dwd.dim_t_employee e on cr.business_scrm_department_id=e.id
left join oe_dwd.dim_t_customer_appeal ca on cr.first_id=ca.customer_relationship_first_id
left join oe_dwd.dim_t_customer c on cc.customer_id=c.id;






"